﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using JigsawService.Parsers;

namespace JigsawService.DataAccess.Security
{
    public class UserAccountDA : JigsawBase
    {
        APIMessage _apimessage;
        string _dbstr;
        
        public UserAccountDA(APIMessage apiMessage)
        {
            _apimessage = apiMessage;
            _dbstr = ConfigurationManager.AppSettings["SecurityDB"].ToString();
        }


        # region Process

        /// <summary>
        /// process method which:
        /// 1) queries useraccount table to check authorization
        /// 2) assigns values from the useraccount record to properties of the APIMessage object
        /// </summary>
        public void ValidateUser()
        {
            _apimessage.StateMessage = "SecurityDA ValidateUser: ";
            DataTable authorization = new DataTable();
            SqlConnection sqlConn = new SqlConnection(_dbstr);
            try
            {

                using (sqlConn)
                {
                    string dmlCmd = "Select GlobalID, Expiration, MethodAccessSummary, DataAccessSummary" +
                                    " From UserAccount" +
                                    " Where RowState = 'Active'" +
                                    " And UserName = @UserName" +
                                    " And Password = @Password";

                    sqlConn.Open();

                    SqlCommand sqlCmd = new SqlCommand(dmlCmd, sqlConn);
                    sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = _apimessage.UserName;
                    sqlCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 250).Value = _apimessage.EncryptedPassword;

                    SqlDataAdapter adap = new SqlDataAdapter(sqlCmd);
                    adap.Fill(authorization);
                }

                // if no rows found, return userid = 0 and authorizationXML = ""
                if (authorization.Rows.Count == 1)
                {
                    _apimessage.ProcessState = StatusCodes.Authorized.ToString();

                    // set property values with useraccount info
                    DateTime expiration = new DateTime();
                    foreach (DataRow dr in authorization.Rows)
                    {
                        expiration = Convert.ToDateTime(dr["Expiration"]);
                        _apimessage.UserID = dr["GlobalID"].ToString();
                        _apimessage.UserAuthorization = dr["MethodAccessSummary"].ToString() + "|" + dr["DataAccessSummary"].ToString();
                    }

                    // check for expired account
                    DateTime currentTime = System.DateTime.UtcNow;
                    if (expiration < currentTime)
                    {
                        _apimessage.StateMessage = "";
                        throw new Exception("Expired");
                    }
                }
                else
                {
                    if (authorization.Rows.Count <= 0)
                    {
                        throw new Exception("SecurityDA ValidateUser: Problem with useraccount authentication or authorization.");
                    }
                    else if (authorization.Rows.Count > 1)
                    {
                        throw new Exception("SecurityDA ValidateUser: Duplicate useraccount records found.");
                    }
                }
            }
            catch (Exception ex)
            {
                // close connection
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
                // rethrow error, to be handled at the service level
                throw;
            }
        }

        /// <summary>
        /// process method which:
        /// 1) queries useraccount table for record details
        /// 2) calls standard update to store new password
        /// </summary>
        public void UpdatePassword(string userID, string userName, string password)
        {
            _apimessage.StateMessage = "SecurityDA UpdatePassword: ";
            
            // query for useraccount record

            // update useraccount record with new password

        }

        /// <summary>
        /// process method which:
        /// 1) queries methodaccess table for account role membership, and builds delimited list of methods
        /// 2) queries dataaccess table for account role membership, and builds delimited list of groups
        /// 3) queries useraccount table for record details, then calls standard update to store delimited access list
        /// </summary>
        public void UpdateAccessSummary(string userID)
        {
            _apimessage.StateMessage = "SecurityDA UpdateAccessSummary: ";

            // query for methodaccess records

            // build delimited list from records returned

            // query for dataaccess records

            // build delimited list from records found

            // query for useraccount record (to insert new version)

            // update useraccount record with new delimited access lists

        }

        /// <summary>
        /// process method which:
        /// 1) creates a new user account
        /// 2) assigns the new account as member of default role
        /// 3) updates the access summary fields of the new account
        /// </summary>
        public void NewUserAccount(string globalid, string username, string password, string firstname, string lastname, string email, string expiration)
        {
            _apimessage.StateMessage = "UserAccountDA UserAccountAction: ";
            SqlCommand sqlCmd = new SqlCommand();

            // assign input parameters as values to the dml parameters
            sqlCmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = globalid;
            sqlCmd.Parameters.Add("@Location", SqlDbType.VarChar, 50).Value = _apimessage.Location;
            sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = username;
            sqlCmd.Parameters.Add("@Password", SqlDbType.VarChar, 250).Value = password;
            sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = firstname;
            sqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = lastname;
            sqlCmd.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = email;
            sqlCmd.Parameters.Add("@Expiration", SqlDbType.DateTime).Value = Convert.ToDateTime(expiration);
            sqlCmd.Parameters.Add("@ModID", SqlDbType.VarChar, 50).Value = _apimessage.UserID;

            // assign dml to command text
            sqlCmd.CommandText = "DECLARE @dupuser INT" +
                                " SET @dupuser = (SELECT count(*) FROM UserAccount WHERE UserName = @UserName AND RowState = 'Active');" +
                                " DECLARE @duprow INT" +
                                " SET @duprow = (SELECT count(*) FROM UserAccount WHERE GlobalID = @GlobalID AND RowState = 'Active');" +
                                " IF (@dupuser = 0 AND @duprow = 0)" +
                                " Insert UserAccount (GlobalID, Location, UserName, Password, FirstName, LastName, Email, Expiration, ModID)" +
                                " Values (@GlobalID, @Location, @UserName, @Password, @FirstName, @LastName, @Email, @Expiration, @ModID)";

            SqlConnection sqlConn = new SqlConnection(_dbstr);
            sqlConn.Open();
            SqlTransaction sqlTran = sqlConn.BeginTransaction();
            try
            {
                using (sqlConn)
                {
                    sqlCmd.Connection = sqlConn;
                    sqlCmd.Transaction = sqlTran;
                    int cmdResult = sqlCmd.ExecuteNonQuery();

                    // insert affects one row, update and delete affect two rows
                    if (cmdResult == 1)
                    {
                        sqlTran.Commit();

                        // set new user as member of default role


                        // update user accesssummary

                        // return new globalid
                        //SetSuccessResult(globalid, "text", _apimessage);
                    }
                    else
                    {
                        // rollback transaction, throw appropriate error message
                        sqlTran.Rollback();
                        throw new Exception("Duplicate found - record was not inserted.");
                    }
                }
            }
            catch (Exception ex)
            {
                // if connection still open, rollback transaction and close connection
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlTran.Rollback();
                    sqlConn.Close();
                }
                // rethrow error, to be handled at the service level
                throw;
            }

        }

        # endregion


        // ********************************************************************
        // Select Section *****************************************************
        // ********************************************************************
        # region Select Section (returns datatable)

        /// <summary>
        /// returns all fields of all active useraccount records
        /// </summary>
        public void GetAllUsers()
        {
            _apimessage.StateMessage = "UserAccountDA GetAllUsers: ";
            SqlCommand scmd = new SqlCommand();

            // build dml command text, assign command text to command object
            scmd.CommandText = "Select *" +
                                " From UserAccount" +
                                " Where RowState = 'Active'";

            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// 
        /// </summary>
        public void GetLastNameLetters()
        {
            _apimessage.StateMessage = "UserAccountDA GetLastNameLetter: ";
            SqlCommand scmd = new SqlCommand();

            // build dml command text, assign command text to command object
            scmd.CommandText = "Select DISTINCT SUBSTRING(LastName, 1, 1) AS Initial" +
                                " From UserAccount" +
                                " Where RowState = 'Active'";

            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// 
        /// </summary>
        public void GetLastInitialUsers(string lastNameLetter)
        {
            _apimessage.StateMessage = "UserAccountDA GetLastInitialUsers: ";
            SqlCommand scmd = new SqlCommand();

            // build dml command text, assign command text to command object
            scmd.CommandText = "Select *" +
                                " From UserAccount" +
                                " Where RowState = 'Active'" +
                                " And SUBSTRING(LastName, 1, 1) = @LastNameInitial";

            // assign parameters and param values to command object (if any)
            scmd.Parameters.Add("@LastNameInitial", SqlDbType.VarChar, 1).Value = lastNameLetter;

            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// returns all fields of specific useraccount record
        /// </summary>
        public void GetUserVersions(string userID)
        {
            _apimessage.StateMessage = "UserAccountDA GetUserVersions: ";
            SqlCommand scmd = new SqlCommand();

            // build dml command text, assign command text to command object
            scmd.CommandText = "Select *" +
                                " From UserAccount" +
                                " Where GlobalID = @GlobalID" +
                                " Order By RowState ASC, GlobalDate DESC";

            // assign parameters and param values to command object (if any)
            scmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = userID;

            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// 
        /// </summary>
        public void GetUser(string userID)
        {
            _apimessage.StateMessage = "UserAccountDA GetUser: ";
            SqlCommand scmd = new SqlCommand();

            // build dml command text, assign command text to command object
            scmd.CommandText = "Select *" +
                                " From UserAccount" +
                                " Where GlobalID = @GlobalID" +
                                " And RowState = 'Active'";

            // assign parameters and param values to command object (if any)
            scmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = userID;

            SelectBase(scmd, _dbstr, _apimessage);
        }


        # endregion

        // ********************************************************************
        // Action Query Section ***********************************************
        // ********************************************************************
        # region Action Section (returns rows affected)

        /// <summary>
        /// Base action query to execute insert, update or delete as transaction
        /// </summary>
        public void UserAccountAction(string actiontype, string globalid, string username, string password, string firstname, string lastname, string email, string expiration, string methodaccesssummary, string dataaccesssummary)
        {
            _apimessage.StateMessage = "UserAccountDA UserAccountAction: ";
            SqlCommand sqlCmd = new SqlCommand();

            // assign input parameters as values to the dml parameters
            sqlCmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = globalid;
            sqlCmd.Parameters.Add("@Location", SqlDbType.VarChar, 50).Value = _apimessage.Location;
            sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = username;
            sqlCmd.Parameters.Add("@Password", SqlDbType.VarChar, 250).Value = password;
            sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = firstname;
            sqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = lastname;
            sqlCmd.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = email;
            sqlCmd.Parameters.Add("@Expiration", SqlDbType.DateTime).Value = Convert.ToDateTime(expiration);
            sqlCmd.Parameters.Add("@MethodAccessSummary", SqlDbType.VarChar, -1).Value = methodaccesssummary;
            sqlCmd.Parameters.Add("@DataAccessSummary", SqlDbType.VarChar, -1).Value = dataaccesssummary;
            sqlCmd.Parameters.Add("@ModID", SqlDbType.VarChar, 50).Value = _apimessage.UserID;

            string errMsg = "UserAccountDA UserAccountAction: ";
            switch (actiontype.ToLower())
            {
                case "insert":
                    // tests for duplicate username and duplicate record, then inserts new record
                    sqlCmd.CommandText = "DECLARE @dupuser INT" +
                                        " SET @dupuser = (SELECT count(*) FROM UserAccount WHERE UserName = @UserName AND RowState = 'Active');" +
                                        " DECLARE @duprow INT" +
                                        " SET @duprow = (SELECT count(*) FROM UserAccount WHERE GlobalID = @GlobalID AND RowState = 'Active');" +
                                        " IF (@dupuser = 0 AND @duprow = 0)" +
                                        " Insert UserAccount (GlobalID, Location, UserName, Password, FirstName, LastName, Email, Expiration, ModID)" +
                                        " Values (@GlobalID, @Location, @UserName, @Password, @FirstName, @LastName, @Email, @Expiration, @ModID)";
                    break;

                case "update":
                    // updates old record, then inserts new version - also sets any existing duplicates to edited state
                    sqlCmd.CommandText = "UPDATE UserAccount SET RowState = 'Edited' WHERE GlobalID = @GlobalID AND RowState = 'Active';" +
                                        " IF (@@ROWCOUNT > 0)" +
                                        " Insert UserAccount (GlobalID, Location, UserName, Password, FirstName, LastName, Email, Expiration, MethodAccessSummary, DataAccessSummary, ModID)" +
                                        " Values (@GlobalID, @Location, @UserName, @Password, @FirstName, @LastName, @Email, @Expiration, @MethodAccessSummary, @DataAccessSummary, @ModID)";
                    break;

                case "delete":
                    // updates old record, then inserts new version with deleted state - also sets any existing duplicates to edited state
                    sqlCmd.CommandText = "UPDATE UserAccount SET RowState = 'Edited' WHERE GlobalID = @GlobalID AND RowState = 'Active';" +
                                        " IF (@@ROWCOUNT > 0)" +
                                        " Insert UserAccount (GlobalID, Location, UserName, Password, FirstName, LastName, Email, Expiration, MethodAccessSummary, DataAccessSummary, RowState, ModID)" +
                                        " Values (@GlobalID, @Location, @UserName, @Password, @FirstName, @LastName, @Email, @Expiration, @MethodAccessSummary, @DataAccessSummary, 'Deleted', @ModID)";
                    break;
            }

            // execute action query
            ActionBase(actiontype.ToLower(), sqlCmd, _dbstr, errMsg, _apimessage, globalid);
        }

        # endregion

    }
}